<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0,viewport-fit=cover"><title>mysqlCookBook02 | 静待花开</title><meta name="author" content="brad 随风起舞"><meta name="copyright" content="brad 随风起舞"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="数据表管理主要内容:  表克隆 从一个表复制到另一个表 使用临时表 生成唯一表名字 决定一个表使用什么存储引擎或者从一个存储引擎转换成另一个  克隆一个表使用CREATE TABLE … LIKE去克隆表结构但是有些异常情况:他不能复制外键的定义,也不能复制任何数据方向(DATA DIRECTION),或索引方向(INDEX DIRECTION)表选项,表可能使用的选项. 克隆出来是一个空表. 从">
<meta property="og:type" content="article">
<meta property="og:title" content="mysqlCookBook02">
<meta property="og:url" content="https://zhangbo4881820.gitee.io/myblog/posts/15287/index.html">
<meta property="og:site_name" content="静待花开">
<meta property="og:description" content="数据表管理主要内容:  表克隆 从一个表复制到另一个表 使用临时表 生成唯一表名字 决定一个表使用什么存储引擎或者从一个存储引擎转换成另一个  克隆一个表使用CREATE TABLE … LIKE去克隆表结构但是有些异常情况:他不能复制外键的定义,也不能复制任何数据方向(DATA DIRECTION),或索引方向(INDEX DIRECTION)表选项,表可能使用的选项. 克隆出来是一个空表. 从">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png">
<meta property="article:published_time" content="2019-11-26T07:47:16.000Z">
<meta property="article:modified_time" content="2023-09-07T09:44:01.900Z">
<meta property="article:author" content="brad 随风起舞">
<meta property="article:tag" content="mysql">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png"><link rel="shortcut icon" href="/myblog/img/favicon.png"><link rel="canonical" href="https://zhangbo4881820.gitee.io/myblog/posts/15287/index.html"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/myblog/css/index.css"><link rel="stylesheet" href="/myblog/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="/myblog/css/fancybox.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = {
  root: '/myblog/',
  algolia: undefined,
  localSearch: {"path":"/myblog/search.xml","preload":false,"top_n_per_article":1,"unescape":false,"languages":{"hits_empty":"找不到您查询的内容：${query}","hits_stats":"共找到 ${hits} 篇文章"}},
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '',
  dateSuffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: undefined,
  source: {
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isAnchor: false,
  percent: {
    toc: true,
    rightside: false,
  },
  autoDarkmode: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: 'mysqlCookBook02',
  isPost: true,
  isHome: false,
  isHighlightShrink: true,
  isToc: true,
  postUpdate: '2023-09-07 17:44:01'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
    win.getCSS = (url,id = false) => new Promise((resolve, reject) => {
      const link = document.createElement('link')
      link.rel = 'stylesheet'
      link.href = url
      if (id) link.id = id
      link.onerror = reject
      link.onload = link.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        link.onload = link.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(link)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const detectApple = () => {
      if(/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
        document.documentElement.classList.add('apple')
      }
    }
    detectApple()
    })(window)</script><meta name="generator" content="Hexo 7.0.0-rc2"><link rel="alternate" href="/myblog/atom.xml" title="静待花开" type="application/atom+xml">
</head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="sidebar-site-data site-data is-center"><a href="/myblog/archives/"><div class="headline">文章</div><div class="length-num">57</div></a><a href="/myblog/tags/"><div class="headline">标签</div><div class="length-num">20</div></a><a href="/myblog/categories/"><div class="headline">分类</div><div class="length-num">25</div></a></div><hr class="custom-hr"/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/myblog/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/myblog/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/myblog/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/myblog/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> 分享</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/myblog/Gallery/"><i class="fa-fw fas fa-images"></i><span> 照片</span></a></li><li><a class="site-page child" href="/myblog/music/"><i class="fa-fw fas fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/myblog/movies/"><i class="fa-fw fas fa-video"></i><span> 视频</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/myblog/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/myblog/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="not-top-img" id="page-header"><nav id="nav"><span id="blog-info"><a href="/myblog/" title="静待花开"><span class="site-name">静待花开</span></a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search" href="javascript:void(0);"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/myblog/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/myblog/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/myblog/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/myblog/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> 分享</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/myblog/Gallery/"><i class="fa-fw fas fa-images"></i><span> 照片</span></a></li><li><a class="site-page child" href="/myblog/music/"><i class="fa-fw fas fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/myblog/movies/"><i class="fa-fw fas fa-video"></i><span> 视频</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/myblog/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/myblog/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div><div id="toggle-menu"><a class="site-page" href="javascript:void(0);"><i class="fas fa-bars fa-fw"></i></a></div></div></nav></header><main class="layout" id="content-inner"><div id="post"><div id="post-info"><h1 class="post-title">mysqlCookBook02</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2019-11-26T07:47:16.000Z" title="发表于 2019-11-26 15:47:16">2019-11-26</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2023-09-07T09:44:01.900Z" title="更新于 2023-09-07 17:44:01">2023-09-07</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/myblog/categories/mysql/">mysql</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="mysqlCookBook02"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"><i class="fa-solid fa-spinner fa-spin"></i></span></span></div></div></div><article class="post-content" id="article-container"><h1 id="数据表管理"><a href="#数据表管理" class="headerlink" title="数据表管理"></a>数据表管理</h1><p><strong>主要内容:</strong></p>
<ol>
<li>表克隆</li>
<li>从一个表复制到另一个表</li>
<li>使用临时表</li>
<li>生成唯一表名字</li>
<li>决定一个表使用什么存储引擎或者从一个存储引擎转换成另一个</li>
</ol>
<h2 id="克隆一个表"><a href="#克隆一个表" class="headerlink" title="克隆一个表"></a>克隆一个表</h2><p>使用<strong>CREATE TABLE … LIKE</strong>去克隆表结构<br>但是有些异常情况:他不能复制外键的定义,也不能复制任何数据方向(DATA DIRECTION),或索引方向(INDEX DIRECTION)表选项,表可能使用的选项.</p>
<p>克隆出来是一个空表.</p>
<h2 id="从一个表复制到另一个表"><a href="#从一个表复制到另一个表" class="headerlink" title="从一个表复制到另一个表"></a>从一个表复制到另一个表</h2><p>使用<strong>INSERT INTO … SELECT</strong>去复制一些或全部来自原始表的行到一个新表<br><code>INSERT INTO new_table SELECT * FROM original_table;</code> 如果要添加适当得部分,那么可以加where条件.<br>如果表不存在可以这么干:<code>CREATE TABLE ... SELECT </code></p>
<h2 id="临时表"><a href="#临时表" class="headerlink" title="临时表"></a>临时表</h2><p>你可能仅仅短时间需要一个表,之后你想要这个表自动消失.那么就用临时表.使用<code>TEMPORARY</code>关键字.如下面创建方式:</p>
<ul>
<li><p>有明确行定义的创建临时表</p>
<pre><code>  CREATE TEMPORARY TABLE tbl_name ( ... column definitions... );
</code></pre>
</li>
<li><p>从一个存在的表创建临时表</p>
<pre><code>  CREATE TEMPORARY TABLE new_table LIKE original_table;
</code></pre>
</li>
<li><p>从一个结果集创建临时表</p>
<pre><code>  CREATE TEMPORARY TABLE tbl_name SELECT ...
</code></pre>
</li>
</ul>
<p><strong>创建临时表有如下警告:</strong></p>
<ul>
<li><p>在一个给定的会话中重新使用临时表,在重新创建它之前你任然必须明确的使用drop删除它.企图创建两个同名的临时表会导致错误发生.</p>
</li>
<li><p>如果修改的临时表“隐藏”了具有相同名称的永久表，请确保使用启用了重新连接功能的编程接口来测试由于连接断开而导致的错误。如果客户端程序在检测到断开的连接后自动重新连接，则修改会影响重新连接后的永久表，而不是临时表。</p>
</li>
<li><p>一些API支持持久连接或连接池。这些可以防止在脚本结束时按预期删除临时表，因为连接保持打开状态以供其他脚本重用。您的脚本无法控制何时关闭连接。这意味着在创建临时表之前执行以下语句是谨慎的做法，以防万一以前执行脚本时该表仍然存在<br><code>DROP TEMPORARY TABLE IF EXISTS tbl_name</code> TEMPORARY关键字很有用,防止误删了永久表</p>
</li>
</ul>
<h2 id="创建唯一名字的表"><a href="#创建唯一名字的表" class="headerlink" title="创建唯一名字的表"></a>创建唯一名字的表</h2><p>创建一个带有名字的表,保证此表在创建之前不存在.整个创建过程脚本清单如下:</p>
<pre><code>SET @tbl_name = CONCAT(&#39;tmp_tbl_&#39;, CONNECTION_ID()); #组装表名CONNECTION_ID()获得连接ID
SET @stmt = CONCAT(&#39;DROP TABLE IF EXISTS &#39;, @tbl_name); # 如果表名存在就删表语句.
PREPARE stmt FROM @stmt; 	# 将这个变量预编译成sql语句
EXECUTE stmt;				# 执行编译后的执行脚本
DEALLOCATE PREPARE stmt;    # 清理stmt的预编译语句
SET @stmt = CONCAT(&#39;CREATE TABLE &#39;, @tbl_name, &#39; (i INT)&#39;);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
</code></pre>
<h2 id="检查或改变表的存储引擎"><a href="#检查或改变表的存储引擎" class="headerlink" title="检查或改变表的存储引擎"></a>检查或改变表的存储引擎</h2><p><strong>查找指定表的存储引擎:</strong></p>
<pre><code>SELECT ENGINE 
FROM
information_schema.`TABLES` 
WHERE
TABLE_schema = &#39;cookbook&#39; 
AND TABLE_name = &#39;mail&#39;;
结果:
ENGINE
InnoDB
或者使用
SHOW TABLE STATUS LIKE &#39;mail&#39;;
</code></pre>
<p><strong>改变一个表的存储引擎:</strong> 使用<strong>ALTER TABLE</strong>和一个指定的存储引擎.例如转换mail表的存储引擎为<strong>MyISAM</strong>使用如下语句:</p>
<pre><code>ALTER TABLE mail ENGINE = MyISAM;
</code></pre>
<p><em>注意:转换一个数据量很大的表的存储引擎可能会花费大量时间并且占用很多cpu和I&#x2F;O</em></p>
<h2 id="使用mysqldump复制表"><a href="#使用mysqldump复制表" class="headerlink" title="使用mysqldump复制表"></a>使用mysqldump复制表</h2><p><strong>可以使用如下命令行命令:</strong></p>
<pre><code>mysqldump cookbook mail &gt; mail.sql
</code></pre>
<p>这就是把mail表输出到mail.sql文件中.</p>
<pre><code>mysql cookbook &lt; mail.sql
</code></pre>
<p>这就是在cookbook数据库中加载mail.sql文件并执行里面的sql语句</p>
<pre><code>mysqldump cookbook mail -ucbuser -p | mysql test -uroot -p
</code></pre>
<p>这里用到了管道.前半部分作为输出到管道.然后管道输入到后半部分.实现不同数据库之间的复制</p>
<h1 id="存储过程-函数-触发器-事件"><a href="#存储过程-函数-触发器-事件" class="headerlink" title="存储过程,函数,触发器,事件"></a>存储过程,函数,触发器,事件</h1><p><em>存储过程和函数</em></p>
<p>存储过程或函数对象为执行操作压缩代码,让你能够通过名字调用他们而不是每次需要时,重复的写代码.</p>
<p>存储函数执行计算并且返回一个可被用于表达式语句中的值.就像函数一样,例如:<strong>RAND()</strong>,<strong>NOW()</strong>,**LEFT()**等.</p>
<p>存储过程中执行不需要返回值的操作.使用CALL语句调用过程，而不在表达式中使用.一个过程可能在一个表中更新行或者生成一个发送给客户端程序的结果集.</p>
<p>存储程序是由用户定义的数据库对象但是为之后的执行存储在服务端.这和从客户端发送sql语句到服务端立即执行不同.每个对象也有被定义在当对象被调用执行其他sql语句的条目中的属性.虽然对象体是单个sql语句,但是复杂语句需要使用组合语句符号(BEGIN…END BLOCK),这里面包含多条语句.因此,对象体处于非常简单到非常复杂的范围.接下来展示简单例子:</p>
<h2 id="存储过程例子"><a href="#存储过程例子" class="headerlink" title="存储过程例子"></a>存储过程例子</h2><p><strong>创建只含单条sql语句的存储过程:</strong></p>
<pre><code>CREATE PROCEDURE show_version() 
SELECT VERSION() AS &#39;MySQL Version&#39;;
</code></pre>
<p><strong>创建复杂语句存储过程</strong></p>
<pre><code>CREATE PROCEDURE show_part_of_day ( ) BEGIN
DECLARE
    cur_time,
    day_part TEXT;

SET cur_time = CURTIME( );
IF
    cur_time &lt; &#39;12:00:00&#39; THEN
        
        SET day_part = &#39;morning&#39;;
    
    ELSEIF cur_time = &#39;12:00:00&#39; THEN
    
    SET day_part = &#39;noon&#39;;
    ELSE 
        SET day_part = &#39;afternoon or night&#39;;
    
END IF;
SELECT
    cur_time,
    day_part;
END;
</code></pre>
<p><strong>调用CALL</strong></p>
<pre><code>call show_version();
call show_part_of_day();
</code></pre>
<p><strong>存储过程操作多个返回值</strong></p>
<p>存储过程使用<strong>OUT</strong>,<strong>INOUT</strong>和用户定义的变量作为参数.一个存储过程不会像函数一样’return’一个值.但是能把值分配到那些参数中去,以便当存储过程调用时用户定义变量能得到想要的值.</p>
<p><strong>存储过程参数三种类型</strong></p>
<ol>
<li><p><strong>IN</strong> 这仅仅是输入参数.这是默认参数类型,如果你没指定参数类型.</p>
</li>
<li><p><strong>OUT</strong> 这是输出参数类型.</p>
</li>
<li><p><strong>INOUT</strong> 这是既能传进参数,又可以传出参数.</p>
</li>
</ol>
<p><strong>代码示例:</strong></p>
<pre><code>CREATE PROCEDURE mail_sender_stats ( IN USER VARCHAR ( 8 ), OUT message INT, OUT total_size INT, OUT avg_size INT ) BEGIN
IF
USER IS NULL THEN
    SELECT
        COUNT( * ),
        IFNULL( SUM( size ), 0 ),
        IFNULL( AVF ( size ), 0 ) INTO message,
        total_size,
        avg_size 
    FROM
        mail;
    ELSE SELECT
        COUNT( * ),
        IFNULL( SUM( size ), 0 ),
        IFNULL( AVG ( size ), 0 ) INTO message,
        total_size,
        avg_size 
    FROM
        mail 
    WHERE
        srcuser = USER;
END IF;
END;
#调用:
CALL mail_sender_stats(&#39;barb&#39;,@messages,@total_size,@avg_size);
SELECT @messages,@total_size,@avg_size;
结果:
@messages @total_size @avg_size
    3	   369550	   123183
</code></pre>
<p>实现动态sql执行</p>
<pre><code>CREATE PROCEDURE exec_stmt ( stmt_str TEXT ) BEGIN
SET @_stmt_str = stmt_str;
PREPARE stmt 
FROM
    @_stmt_str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
# 因为PREPARE接受仅当指定的一个文字字符串或一个用户定义的变量语句.存储在程序参数中的语句不起作用
执行:
SET @tbl_name=&#39;mytable&#39;;
SET @val=5;
CALL exec_stmt(CONCAT(&#39;CREATE TABLE &#39;,@tbl_name,&#39; (i INT)&#39;)); 
CALL exec_stmt(CONCAT(&#39;INSERT INTO &#39;,@tbl_name,&#39; (i) VALUES(&#39;,@val,&#39;)&#39;));
</code></pre>
<p>让sql更安全点:</p>
<pre><code>CREATE FUNCTION quote_identifier ( id TEXT ) 
RETURNS TEXT DETERMINISTIC 
RETURN CONCAT( &#39;`&#39;, REPLACE ( id, &#39;`&#39;, &#39;``&#39; ), &#39;`&#39; );

SET @tbl_name = quote_identifier(@tbl_name); 
SET @val = QUOTE(@val);

CALL exec_stmt(CONCAT(&#39;CREATE TABLE &#39;,@tbl_name,&#39; (i INT)&#39;)); 
CALL exec_stmt(CONCAT(&#39;INSERT INTO &#39;,@tbl_name,&#39; (i) VALUES(&#39;,@val,&#39;)&#39;));
</code></pre>
<p>循环游标计数例子</p>
<pre><code>CREATE PROCEDURE mail_num ( ) BEGIN
DECLARE
    done BOOLEAN DEFAULT FALSE;
DECLARE
    state_pop,
    total_pop BIGINT DEFAULT 0;
DECLARE
    cur CURSOR FOR SELECT
    size 
FROM
    mail;
DECLARE
    CONTINUE HANDLER FOR NOT FOUND 
    SET done = TRUE;
OPEN cur;
fetch_loop : LOOP
FETCH cur INTO state_pop;
IF
    done THEN
        LEAVE fetch_loop;
    
END IF;

SET total_pop = total_pop + state_pop;

END LOOP;
CLOSE cur;
SELECT
    total_pop AS &#39;Total size&#39;;
END;
</code></pre>
<p>错误处理的存储过程:</p>
<pre><code>CREATE DEFINER=`root`@`localhost` PROCEDURE `drop_user`( USER TEXT, HOST TEXT )
BEGIN
DECLARE
account TEXT;
DECLARE
CONTINUE HANDLER FOR 1396 SELECT
CONCAT( &#39;Unknown user: &#39;, account ) AS Message;
SET account = CONCAT( QUOTE( USER ), &#39;@&#39;, QUOTE( HOST ) );
CALL exec_stmt ( CONCAT( &#39;DROP USER &#39;, account ) );
END
</code></pre>
<h2 id="存储函数例子"><a href="#存储函数例子" class="headerlink" title="存储函数例子"></a>存储函数例子</h2><p><strong>简单函数</strong></p>
<pre><code>CREATE FUNCTION avg_mail_size()
RETURNS FLOAT READS SQL DATA
RETURN (SELECT avg(size) FROM mail);
# RETURNS FLOAT指定了方法返回值的类型,READS SQL DATA指定了这个方法只读不会修改数据
# 最后一个RETURN 返回子查询查到的结果给调用者.(每个存储方法必须至少有一个return)
</code></pre>
<p><strong>复杂函数</strong></p>
<pre><code>CREATE FUNCTION avg_mail_size_new ( USER VARCHAR ( 8 ) ) RETURNS FLOAT READS SQL DATA BEGIN
DECLARE
    avg FLOAT;
IF
USER IS NULL THEN
        
        SET avg = ( SELECT AVG( size ) FROM mail );
    ELSE 
        SET avg = ( SELECT AVG( size ) FROM mail WHERE srcuser = USER );
    
END IF;
RETURN avg;
END;
# 就是给函数一个参数如果是null那么返回所有数据的平均值.
# 如果不为空,就返回指定的srcuser字段的size平均值.

# 这是一个取税率的函数.CONTINUE HANDLER FOR NOT FOUND 这就是当表中找不到rate时 rate=0
CREATE FUNCTION sales_tax_rate ( state_code CHAR ( 2 ) ) RETURNS DECIMAL ( 3, 2 ) READS SQL DATA BEGIN
DECLARE
    rate DECIMAL ( 3, 2 );
DECLARE
    CONTINUE HANDLER FOR NOT FOUND 
    SET rate = 0;
SELECT
    tax_rate INTO rate 
FROM
    sales_tax_rate 
WHERE
    state = state_code;
RETURN rate;
END;
</code></pre>
<h2 id="触发器"><a href="#触发器" class="headerlink" title="触发器"></a>触发器</h2><p><em>触发器(Triggers)</em></p>
<p>触发器是一个活动对象,当一个表通过<strong>INSERT</strong>,<strong>UPDATE</strong>,<strong>DELETE</strong>语句修改时候.每当你你修改一个表时候,触发器自动完成指定动作以便你不需要记得自己去做他们.</p>
<p><strong>代码例子:</strong></p>
<ol>
<li><p>创建一个插入之前的触发器</p>
<pre><code> CREATE TRIGGER bi_cust_invoice BEFORE INSERT ON cust_invoice FOR EACH ROW
 SET NEW.tax - rate = sales_tax_rate ( NEW.state );
</code></pre>
</li>
<li><p>使用触发器模拟基于函数的索引</p>
<pre><code> #先建个表来演示例子
 CREATE TABLE expdata ( 
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
 value FLOAT, # 原始值 
 log10_value FLOAT, # LOG10() 函数的原始值 
 INDEX (value), # 原始值索引 
 INDEX (log10_value) # 基于方法的索引值 
 );
 #在插入前和更新前创建触发器,填充log10_value的值
 CREATE TRIGGER bi_expdata BEFORE INSERT ON expdata FOR EACH ROW
 SET NEW.log10_value = LOG10( NEW.VALUE );

 CREATE TRIGGER bu_expdata BEFORE UPDATE ON expdata FOR EACH ROW
 SET NEW.log10_value = LOG10( NEW.VALUE );

 # 然后使用插入或更新语句测试结果
 INSERT INTO expdata (value) VALUES (.01),(.1),(1),(10),(100);
 SELECT * FROM expdata;
</code></pre>
</li>
<li><p>使用触发器提供一个合适的当前时间和日期</p>
<pre><code> #先建个表来演示例子
 CREATE TABLE ts_emulate ( DATA CHAR ( 10 ), d DATE, t TIME );

 # 在插入数据之前填充d和t
 CREATE TRIGGER bi_ts_emulate BEFORE INSERT ON ts_emulate FOR EACH ROW
 SET NEW.d = CURDATE( ),
 NEW.t = CURTIME( );

 # 在更新之前,如果数据改变就改边d和t的值
 CREATE TRIGGER bu_ts_emulate BEFORE UPDATE ON ts_emulate FOR EACH ROW
 IF
 NEW.DATA &lt;&gt; OLD.DATA THEN
 SET NEW.d = CURDATE( ),
 NEW.t = CURTIME( );
 END IF;

 #插入测试
 INSERT INTO ts_emulate (data) VALUES(&#39;cat&#39;);
 INSERT INTO ts_emulate (data) VALUES(&#39;dog&#39;);

 SELECT * FROM ts_emulate;
 结果:
 data   d 			t
 cat	2019-11-27	11:31:39
 dog	2019-11-27	11:31:39

 #更新测试
 UPDATE ts_emulate SET data = &#39;axolotl&#39; WHERE data = &#39;cat&#39;;
 SELECT * FROM ts_emulate;
 结果:
 data       d 			t
 axolotl	2019-11-27	11:33:18
 dog	    2019-11-27	11:31:39
</code></pre>
</li>
<li><p>使用触发器捕获表的变化,记录到单独的log表中</p>
<pre><code> # 先建表,这表包含当前拍卖的信息.ts反应最近bid更新时间.
 # bid反应拍卖过程中的价格,最终拍卖结束这个值为最终值,并且得到最终值后可以删
 CREATE TABLE auction (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 item VARCHAR ( 30 ) NOT NULL,
 bid DECIMAL ( 10, 2 ) NOT NULL,
 PRIMARY KEY (id)
 );

 # 然后创建一个日志表,记录拍卖表的历史变化
 # 它与拍卖表有两点不同
 # 1.它包含一个活动列用来表明每行发生了哪种改变
 # 2.id列非唯一索引,每个id可以存在于多行中,因为给定的拍卖表可以在log表中生成许多行
 CREATE TABLE auction_log (
 action ENUM ( &#39;create&#39;, &#39;update&#39;, &#39;delete&#39; ),
 id INT UNSIGNED NOT NULL,
 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 item VARCHAR ( 30 ) NOT NULL,
 bid DECIMAL ( 10, 2 ) NOT NULL,
 INDEX ( id ) 
 );

 # 最后创建一系列的触发器
 CREATE TRIGGER ai_auction AFTER INSERT ON auction FOR EACH ROW
 INSERT INTO auction_log ( action, id, ts, item, bid )
 VALUES
 ( &#39;create&#39;, NEW.id, NOW( ), NEW.item, NEW.bid );
 CREATE TRIGGER au_auction AFTER UPDATE ON auction FOR EACH ROW
 INSERT INTO auction_log ( action, id, ts, item, bid )
 VALUES
 ( &#39;update&#39;, NEW.id, NOW( ), NEW.item, NEW.bid );
 CREATE TRIGGER ad_auction AFTER DELETE ON auction FOR EACH ROW
 INSERT INTO auction_log ( action, id, ts, item, bid )
 VALUES
 ( &#39;delete&#39;, OLD.id, OLD.ts, OLD.item, OLD.bid );

 #测试
 INSERT INTO auction (item,bid) VALUES(&#39;chintz pillows&#39;,5.00);
 UPDATE auction SET bid = 7.50 WHERE id = LAST_INSERT_ID();
 一段时间后
 UPDATE auction SET bid = 9.00 WHERE id = LAST_INSERT_ID();
 一段时间后
 DELETE FROM auction WHERE id = LAST_INSERT_ID();

 SELECT * FROM auction_log WHERE id = LAST_INSERT_ID() ORDER BY ts;
 结果:
 create	1	2019-11-27 11:59:57	chintz pillows	5.00
 update	1	2019-11-27 12:02:34	chintz pillows	7.50
 update	1	2019-11-27 12:05:39	chintz pillows	9.00
 delete	1	2019-11-27 12:05:39	chintz pillows	9.00
</code></pre>
</li>
<li><p>触发器处理错误</p>
<pre><code> #建个表
 CREATE TABLE contact_info (
     id INT NOT NULL AUTO_INCREMENT,
     NAME VARCHAR ( 30 ),# state of residence
     state CHAR ( 2 ),# state of residence
     email VARCHAR ( 50 ),# email address
     url VARCHAR ( 255 ),# web address
 PRIMARY KEY ( id ) 
 );

 #建个触发器,验证州是否合法,邮件是否合法.
 CREATE TRIGGER bi_contact_info BEFORE INSERT ON contact_info FOR EACH ROW
 BEGIN
     IF
         ( SELECT COUNT( * ) FROM states WHERE abbrev = NEW.state ) = 0 THEN
             SIGNAL SQLSTATE &#39;HY000&#39; 
             SET MYSQL_ERRNO = 1525,
             MESSAGE_TEXT = &#39;invalid state code&#39;;
     END IF;
     IF
         INSTR( NEW.email, &#39;@&#39; ) = 0 THEN
             SIGNAL SQLSTATE &#39;HY000&#39; 
             SET MYSQL_ERRNO = 1525,
             MESSAGE_TEXT = &#39;invalid email address&#39;;
     END IF;
     SET NEW.url = TRIM( LEADING &#39;http://&#39; FROM NEW.url );
 END;
</code></pre>
</li>
</ol>
<h2 id="事件-依据计划-Scheduled-的事件"><a href="#事件-依据计划-Scheduled-的事件" class="headerlink" title="事件(依据计划(Scheduled)的事件)"></a>事件(依据计划(Scheduled)的事件)</h2><p><em>事件(Scheduled events)</em></p>
<p>事件是一个执行sql语句的对象以一个计划的时间或次数</p>
<p>例如,事件可以帮助你执行任务管理,如:阶段性的删除老表行或每晚创建汇总表.</p>
<p><strong>代码例子:</strong></p>
<ol>
<li><p>创建一个事件依照计划时间执行</p>
<pre><code> # 建一个演示表
 CREATE TABLE mark_log ( 
     ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
     message VARCHAR ( 100 ) 
     );

 # 创建一个事件,每5分钟往mark_log表中插入message
 CREATE EVENT mark_insert
 ON SCHEDULE EVERY 5 MINUTE
 DO INSERT INTO mark_log (message) VALUES (&#39;-- MARK --&#39;)

 #删除事件
 DROP EVENT mark_insert;

 # 停用事件
 ALTER EVENT mark_insert DISABLE;

 # 恢复停用事件到可用
 ALTER EVENT mark_insert ENABLE;

 # 创建一个过期删行事件
 CREATE EVENT mark_expire ON SCHEDULE EVERY 1 DAY DO
 DELETE 
 FROM
     mark_log 
 WHERE
     ts &lt; NOW( ) - INTERVAL 2 DAY;
</code></pre>
</li>
</ol>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="https://zhangbo4881820.gitee.io/myblog">brad 随风起舞</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://zhangbo4881820.gitee.io/myblog/posts/15287/">https://zhangbo4881820.gitee.io/myblog/posts/15287/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="https://zhangbo4881820.gitee.io/myblog" target="_blank">静待花开</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/myblog/tags/mysql/">mysql</a></div><div class="post_share"><div class="social-share" data-image="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/social-share.js/1.0.16/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdnjs.cloudflare.com/ajax/libs/social-share.js/1.0.16/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/myblog/posts/64374/" title="mysqlCookBook03"><div class="cover" style="background: var(--default-bg-color)"></div><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">mysqlCookBook03</div></div></a></div><div class="next-post pull-right"><a href="/myblog/posts/15095/" title="mysqlCookBook01"><div class="cover" style="background: var(--default-bg-color)"></div><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">mysqlCookBook01</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span>相关推荐</span></div><div class="relatedPosts-list"><div><a href="/myblog/posts/17440/" title="mysql-index"><div class="cover" style="background: var(--default-bg-color)"></div><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2019-11-14</div><div class="title">mysql-index</div></div></a></div><div><a href="/myblog/posts/15095/" title="mysqlCookBook01"><div class="cover" style="background: var(--default-bg-color)"></div><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2019-11-25</div><div class="title">mysqlCookBook01</div></div></a></div><div><a href="/myblog/posts/64374/" title="mysqlCookBook03"><div class="cover" style="background: var(--default-bg-color)"></div><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2019-11-27</div><div class="title">mysqlCookBook03</div></div></a></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="is-center"><div class="avatar-img"><img src="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png" onerror="this.onerror=null;this.src='/myblog/img/friend_404.gif'" alt="avatar"/></div><div class="author-info__name">brad 随风起舞</div><div class="author-info__description">有些事情需要一辈子的沉淀</div></div><div class="card-info-data site-data is-center"><a href="/myblog/archives/"><div class="headline">文章</div><div class="length-num">57</div></a><a href="/myblog/tags/"><div class="headline">标签</div><div class="length-num">20</div></a><a href="/myblog/categories/"><div class="headline">分类</div><div class="length-num">25</div></a></div><a id="card-info-btn" href="https://github.com/xxxxxx"><i class="fab fa-github"></i><span>Follow Me</span></a><div class="card-info-social-icons is-center"><a class="social-icon" href="https://github.com/xxxxx" target="_blank" title="Github"><i class="fab fa-github" style="color: #24292e;"></i></a><a class="social-icon" href="mailto:xxxxxx@gmail.com" target="_blank" title="Email"><i class="fas fa-envelope" style="color: #4a7dbe;"></i></a></div></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn fa-shake"></i><span>公告</span></div><div class="announcement_content">This is my Blog</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E8%A1%A8%E7%AE%A1%E7%90%86"><span class="toc-number">1.</span> <span class="toc-text">数据表管理</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%85%8B%E9%9A%86%E4%B8%80%E4%B8%AA%E8%A1%A8"><span class="toc-number">1.1.</span> <span class="toc-text">克隆一个表</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BB%8E%E4%B8%80%E4%B8%AA%E8%A1%A8%E5%A4%8D%E5%88%B6%E5%88%B0%E5%8F%A6%E4%B8%80%E4%B8%AA%E8%A1%A8"><span class="toc-number">1.2.</span> <span class="toc-text">从一个表复制到另一个表</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B8%B4%E6%97%B6%E8%A1%A8"><span class="toc-number">1.3.</span> <span class="toc-text">临时表</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E5%94%AF%E4%B8%80%E5%90%8D%E5%AD%97%E7%9A%84%E8%A1%A8"><span class="toc-number">1.4.</span> <span class="toc-text">创建唯一名字的表</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%A3%80%E6%9F%A5%E6%88%96%E6%94%B9%E5%8F%98%E8%A1%A8%E7%9A%84%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E"><span class="toc-number">1.5.</span> <span class="toc-text">检查或改变表的存储引擎</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BD%BF%E7%94%A8mysqldump%E5%A4%8D%E5%88%B6%E8%A1%A8"><span class="toc-number">1.6.</span> <span class="toc-text">使用mysqldump复制表</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B-%E5%87%BD%E6%95%B0-%E8%A7%A6%E5%8F%91%E5%99%A8-%E4%BA%8B%E4%BB%B6"><span class="toc-number">2.</span> <span class="toc-text">存储过程,函数,触发器,事件</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E4%BE%8B%E5%AD%90"><span class="toc-number">2.1.</span> <span class="toc-text">存储过程例子</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%AD%98%E5%82%A8%E5%87%BD%E6%95%B0%E4%BE%8B%E5%AD%90"><span class="toc-number">2.2.</span> <span class="toc-text">存储函数例子</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="toc-number">2.3.</span> <span class="toc-text">触发器</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BA%8B%E4%BB%B6-%E4%BE%9D%E6%8D%AE%E8%AE%A1%E5%88%92-Scheduled-%E7%9A%84%E4%BA%8B%E4%BB%B6"><span class="toc-number">2.4.</span> <span class="toc-text">事件(依据计划(Scheduled)的事件)</span></a></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/myblog/posts/15695/" title="jenkins（1）">jenkins（1）</a><time datetime="2023-09-14T02:17:44.000Z" title="发表于 2023-09-14 10:17:44">2023-09-14</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/myblog/posts/22012/" title="wsl的Ubuntu子系统（1）">wsl的Ubuntu子系统（1）</a><time datetime="2023-09-13T08:42:19.000Z" title="发表于 2023-09-13 16:42:19">2023-09-13</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/myblog/posts/30302/" title="effective-java-(接口优于抽象类)">effective-java-(接口优于抽象类)</a><time datetime="2023-09-12T08:12:48.000Z" title="发表于 2023-09-12 16:12:48">2023-09-12</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/myblog/posts/9473/" title="effective-java(创建和销毁对象--组合的形式优于继承)">effective-java(创建和销毁对象--组合的形式优于继承)</a><time datetime="2023-09-11T08:23:19.000Z" title="发表于 2023-09-11 16:23:19">2023-09-11</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/myblog/posts/16107/" title="Hello World">Hello World</a><time datetime="2023-09-07T09:44:01.910Z" title="发表于 2023-09-07 17:44:01">2023-09-07</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2023 By brad 随风起舞</div><div class="framework-info"><span>框架 </span><a href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><span class="scroll-percent"></span><i class="fas fa-arrow-up"></i></button></div></div><div><script src="/myblog/js/utils.js"></script><script src="/myblog/js/main.js"></script><script src="/myblog/js/fancybox.umd.js"></script><div class="js-pjax"></div><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script><div id="local-search"><div class="search-dialog"><nav class="search-nav"><span class="search-dialog-title">搜索</span><span id="loading-status"></span><button class="search-close-button"><i class="fas fa-times"></i></button></nav><div class="is-center" id="loading-database"><i class="fas fa-spinner fa-pulse"></i><span>  数据库加载中</span></div><div class="search-wrap"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div><hr/><div class="no-result" id="local-search-results"></div><div id="local-search-stats-wrap"></div></div></div><div id="search-mask"></div><script src="/myblog/js/search/local-search.js"></script></div></div></body></html>